package com.yueqian.Dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.yueqian.Entity.Employee;
import com.yueqian.Utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

/**
 * 查询员工信息
 */
public class GetEmployee {
    /**
     * 查询所有员工信息
     */
    public static List<Employee> getAllEmployee() {
        List<Employee> employees = new ArrayList<>();
        try {
            //连接
            Connection connection = JDBCUtils.Connection();
            String sql = "select * from employee";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Employee employee = new Employee();
                String number = resultSet.getString("number");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                employee.setNumber(number);
                employee.setUsername(username);
                employee.setPassword(password);
                System.out.println(employee);
                employees.add(employee);
            }
            return employees;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    /**
     * 条件查询
     * @param username
     * @param number
     * @return
     */
    public Employee getEmployee(String username, String number) {
        try {
            Connection connection = JDBCUtils.Connection();
            String sql = "select * from employee where username like ? and number = ?";
            String sql1 = "select * from employee where username like ? ";
            String sql2 = "select * from employee where number = ?";
            ResultSet resultSet = null;
            if(username != "" && number != "")
            {        PreparedStatement preparedStatement = connection.prepareStatement(sql);
                StringBuilder username1 = new StringBuilder(username);
                username1.append("%");
                username1.insert(0, "%");
                preparedStatement.setString(1, String.valueOf(username1));
            preparedStatement.setString(2, number);
             resultSet = preparedStatement.executeQuery();}
            else if(username != ""&& number == "")
            {
                System.out.println("查询2中");
                PreparedStatement preparedStatement = connection.prepareStatement(sql1);
                StringBuilder username1 = new StringBuilder(username);
                username1.append("%");
                username1.insert(0, "%");
                preparedStatement.setString(1, String.valueOf(username1));
                 resultSet = preparedStatement.executeQuery();
            }
            else if(username == ""&& number != "")
            {
                PreparedStatement preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1, number);
                 resultSet = preparedStatement.executeQuery();
            }

            if (resultSet.next()) {
                Employee employee = new Employee();
                String number1 = resultSet.getString("number");
                username = resultSet.getString("username");
                String password = resultSet.getString("password");
                employee.setNumber(number1);
                employee.setUsername(username);
                employee.setPassword(password);
                System.out.println(employee);
                return employee;
            }
            else {
                System.out.println("用户不存在");
                return null;
            }
        } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    }
    /**
     * 获取信息量
     */
    public int getTotalCount() {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select count(*) from employee";
        try {
            Number number = queryRunner.query(JDBCUtils.Connection(), sql, new ScalarHandler<Number>());
            return number.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 分页查询
     */
    public List<Employee> getPageUsers(int pageNumber, int pageSize) {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from employee     limit ?,?";
        try {
            List<Employee> employees = queryRunner.query(JDBCUtils.Connection(), sql, new BeanListHandler<Employee>(Employee.class), (pageNumber - 1) * pageSize, pageSize);
            return employees;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
